package com.iplant.basic.serviceImpl.dao.bms;

import java.util.ArrayList;
import java.util.Calendar;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.jdbc.core.namedparam.MapSqlParameterSource;
import org.springframework.jdbc.core.namedparam.SqlParameterSource;
import org.springframework.jdbc.support.GeneratedKeyHolder;
import org.springframework.jdbc.support.KeyHolder;

import com.iplant.base.po.OutResult;
import com.iplant.base.po.Pagination;
import com.iplant.base.po.enums.DBSchemas;
import com.iplant.base.po.enums.ExceptionTypes;
import com.iplant.base.utils.general.StringUtils;
import com.iplant.base.utils.sql.BaseDAO; 
import com.iplant.basic.service.po.bms.BMSEmployee;
import com.iplant.basic.service.po.bms.BMSPosition;

public class BMSPositionDAO extends BaseDAO {
	private static BMSPositionDAO Instance = null;

	private static Logger logger = LoggerFactory.getLogger(BMSPositionDAO.class);

	private BMSPositionDAO() {
		super();
	}

	public static BMSPositionDAO getInstance() {
		if (Instance == null)
			Instance = new BMSPositionDAO();
		return Instance;
	}

	private BMSPosition BMS_CheckPositionName(BMSEmployee wLoginUser, int wID, int wDepartmentID, String wName,
			OutResult<Integer> wErrorCode) {
		BMSPosition wPosition = new BMSPosition();
		wErrorCode.set(0);
		try {

			Map<String, Object> wParams = new HashMap<String, Object>();
			String wSQLText = "";
			if (wID > 0) {
				wSQLText = StringUtils.Format(
						"Select * from {0}.bms_position where ID!=:ID and Name=:Name and DepartmentID=:DepartmentID;",
						super.getDBName());

			} else {
				wSQLText = StringUtils.Format(
						"Select * from {0}.bms_position where Name=:Name and DepartmentID=:DepartmentID;",
						super.getDBName());

			}
			wParams.put("ID", wID);
			wParams.put("Name", wName);
			wParams.put("DepartmentID", wDepartmentID);
			wSQLText = this.DMLChange(wSQLText);
			List<Map<String, Object>> wQueryResultList = super.queryForList(wSQLText, wParams);
			for (Map<String, Object> wSqlDataReader : wQueryResultList) {
				wPosition.ID = StringUtils.parseInt(wSqlDataReader.get("ID"));
				wPosition.Name = StringUtils.parseString(wSqlDataReader.get("Name"));
				wPosition.Code = StringUtils.parseString(wSqlDataReader.get("Code"));
				wPosition.DepartmentID = StringUtils.parseInt(wSqlDataReader.get("DepartmentID"));
				wPosition.Active = StringUtils.parseInt(wSqlDataReader.get("Active"));
				wPosition.CreatorID = StringUtils.parseInt(wSqlDataReader.get("CreatorID"));
				wPosition.EditorID = StringUtils.parseInt(wSqlDataReader.get("EditorID"));
				wPosition.EditTime = StringUtils.parseCalendar(wSqlDataReader.get("EditTime"));
				wPosition.CreateTime = StringUtils.parseCalendar(wSqlDataReader.get("CreateTime"));
				wPosition.DutyID = StringUtils.parseInt(wSqlDataReader.get("DutyID"));

				if (StringUtils.isEmpty(wPosition.Code)) {
					wPosition.Code = StringUtils.Format("POS{0}", String.format("%07d", wPosition.ID));
				}
			}

		} catch (Exception ex) {
			wErrorCode.set(ExceptionTypes.DBSQL.getValue());
			logger.error("BMS_CheckPositionName", ex);
		}
		return wPosition;
	}

	public void BMS_AddPosition(BMSEmployee wLoginUser, BMSPosition wPosition, OutResult<Integer> wErrorCode) {

		wErrorCode.set(0);

		try {

			BMSPosition wPositionO = this.BMS_CheckPositionName(wLoginUser, 0, wPosition.DepartmentID, wPosition.Name,
					wErrorCode);
			if (wPosition.Name.length() < 1 || wPositionO.ID > 0) {
				wErrorCode.set(ExceptionTypes.Logic.getValue());
				return;
			}

			Map<String, Object> wParams = new HashMap<String, Object>();

			String wSQLText = StringUtils.Format("Insert Into {0}.bms_position", super.getDBName())
					+ "(Name,CreatorID,CreateTime, EditorID,EditTime,Active,DepartmentID,DutyID,Description) "
					+ " Values(:Name,:CreatorID,:CreateTime,:EditorID,:EditTime,:Active,:DepartmentID,:DutyID,:Description);";
			wParams.clear();

			wParams.put("Name", wPosition.Name);
			wParams.put("CreatorID", wLoginUser.getID());
			wParams.put("CreateTime", Calendar.getInstance());
			wParams.put("EditorID", wLoginUser.getID());
			wParams.put("EditTime", Calendar.getInstance());
			wParams.put("Active", wPosition.Active);
			wParams.put("DepartmentID", wPosition.DepartmentID);
			wParams.put("DutyID", wPosition.DutyID);
			wParams.put("Description", wPosition.Description);
			wSQLText = this.DMLChange(wSQLText);
			KeyHolder keyHolder = new GeneratedKeyHolder();

			SqlParameterSource wSqlParameterSource = new MapSqlParameterSource(wParams);
			super.update(wSQLText, wSqlParameterSource, keyHolder);

			wPosition.ID = keyHolder.getKey().intValue();

		} catch (Exception ex) {
			logger.error("BMS_AddPosition", ex);
			wErrorCode.set(ExceptionTypes.DBSQL.getValue());
		}
	}

	public void BMS_SavePosition(BMSEmployee wLoginUser, BMSPosition wPosition, OutResult<Integer> wErrorCode) {
		wErrorCode.set(0);

		try {

			BMSPosition wPositionO = this.BMS_CheckPositionName(wLoginUser, wPosition.ID, wPosition.DepartmentID,
					wPosition.Name, wErrorCode);
			if (wPosition.Name.length() < 1 || wPositionO.ID > 0) {
				wErrorCode.set(ExceptionTypes.Logic.getValue());
				return;
			}

			Map<String, Object> wParams = new HashMap<String, Object>();
			String wSQLText = StringUtils.Format("Update {0}.bms_position", super.getDBName())
					+ " Set Name=:Name,Code=:Code,EditorID=:EditorID,DepartmentID=:DepartmentID,Description=:Description,"
					+ "EditTime=:EditTime, DutyID=:DutyID where ID=:ID ;";
			wParams.clear();

			wParams.put("Name", wPosition.Name);
			wParams.put("EditorID", wLoginUser.getID());
			wParams.put("EditTime", Calendar.getInstance());
			wParams.put("DepartmentID", wPosition.DepartmentID);
			wParams.put("DutyID", wPosition.DutyID);
			wParams.put("Description", wPosition.Description);
			wParams.put("ID", wPosition.ID);
			wParams.put("Code", StringUtils.Format("POS{0}", String.format("%07d", wPosition.ID)));
			wSQLText = this.DMLChange(wSQLText);
			super.update(wSQLText, wParams);

		} catch (Exception ex) {
			logger.error("BMS_SavePosition", ex);
			wErrorCode.set(ExceptionTypes.DBSQL.getValue());
		}
	}

	public int BMS_ActivePositionByID(BMSEmployee wLoginUser, int wID, OutResult<Integer> wErrorCode) {
		wErrorCode.set(0);

		try {

			Map<String, Object> wParams = new HashMap<String, Object>();

			String wSQLText = StringUtils.Format("Update {0}.bms_position", super.getDBName())
					+ " Set Code=:Code, EditorID=:EditorID,EditTime=:EditTime,Active=1 where ID=:ID";
			wParams.clear();

			wParams.put("EditorID", wLoginUser.getID());
			wParams.put("EditTime", Calendar.getInstance());
			wParams.put("ID", wID);

			wParams.put("Code", StringUtils.Format("POS{0}", String.format("%07d", wID)));
			wSQLText = this.DMLChange(wSQLText);
			super.update(wSQLText, wParams);

		} catch (Exception ex) {
			logger.error("BMS_ActivePositionByID", ex);
			wErrorCode.set(ExceptionTypes.DBSQL.getValue());
		}
		return wErrorCode.Result;
	}

	public int BMS_DisablePositionByID(BMSEmployee wLoginUser, int wID, OutResult<Integer> wErrorCode) {
		wErrorCode.set(0);
		try {

			Map<String, Object> wParams = new HashMap<String, Object>();

			String wSQLText = StringUtils.Format("Update {0}.bms_position", super.getDBName())
					+ " Set  Code=:Code,EditorID=:EditorID,EditTime=:EditTime,Active=2 where ID=:ID;";
			wParams.clear();

			wParams.put("EditorID", wLoginUser.getID());
			wParams.put("EditTime", Calendar.getInstance());
			wParams.put("ID", wID);
			wParams.put("Code", StringUtils.Format("POS{0}", String.format("%07d", wID)));
			wSQLText = this.DMLChange(wSQLText);
			super.update(wSQLText, wParams);

		} catch (Exception ex) {
			logger.error("BMS_DisablePositionByID", ex);
			wErrorCode.set(ExceptionTypes.DBSQL.getValue());
		}
		return wErrorCode.Result;
	}

	public List<BMSPosition> BMS_QueryPositionList(BMSEmployee wLoginUser, int wDepartmentID, String wName, int wActive,
			Pagination wPagination, OutResult<Integer> wErrorCode) {
		return BMS_QueryPositionList(wLoginUser, -1, "", wDepartmentID, wName, wActive, wPagination, wErrorCode);
	}

	private List<BMSPosition> BMS_QueryPositionList(BMSEmployee wLoginUser, int wID, String wCode, int wDepartmentID,
			String wName, int wActive, Pagination wPagination, OutResult<Integer> wErrorCode) {
		wErrorCode.set(0);
		List<BMSPosition> wPositionList = new ArrayList<BMSPosition>();

		try {

			Map<String, Object> wParams = new HashMap<String, Object>();

			String wSQLText = StringUtils.Format("Select t.*,t2.Name as DepartmentName ,t3.Name as CreatorName ,"
					+ " t4.Name as EditorName  from {0}.bms_position t "
					+ " left join {0}.bms_department t2 on t.DepartmentID=t2.ID "
					+ " left join {0}.mbs_user t3 on t.CreatorID=t3.ID "
					+ " left join {0}.mbs_user t4 on t.EditorID=t4.ID "
					+ " where (:ID<=0 or  t.ID=:ID)  and (:Code = '''' or  t.Code = :Code)"
					+ " AND (:Active<0 or  t.Active=:Active) "
					+ " AND (:DepartmentID<=0 or  t.DepartmentID=:DepartmentID) "
					+ " AND (:Name = '''' or  t.Name like :Name)",
					super.getDBName());
			wParams.clear();

			wParams.put("ID", wID);
			wParams.put("Code", wCode);
			wParams.put("Active", wActive);
			wParams.put("DepartmentID", wDepartmentID);
			wParams.put("Name", StringUtils.isEmpty(wName) ? "" : ("%" + wName + "%"));
			wSQLText = this.DMLChange(wSQLText);
			List<Map<String, Object>> wQueryResultList = super.queryForList(wSQLText, wParams,wPagination);
			for (Map<String, Object> wSqlDataReader : wQueryResultList) {
				BMSPosition wPosition = new BMSPosition();
				wPosition.ID = StringUtils.parseInt(wSqlDataReader.get("ID"));
				wPosition.Name = StringUtils.parseString(wSqlDataReader.get("Name"));

				wPosition.Code = StringUtils.parseString(wSqlDataReader.get("Code"));
				wPosition.CreatorID = StringUtils.parseInt(wSqlDataReader.get("CreatorID"));
				wPosition.EditorID = StringUtils.parseInt(wSqlDataReader.get("EditorID"));
				wPosition.CreatorName = StringUtils.parseString(wSqlDataReader.get("CreatorName"));
				wPosition.EditorName = StringUtils.parseString(wSqlDataReader.get("EditorName"));
				wPosition.CreateTime = StringUtils.parseCalendar(wSqlDataReader.get("CreateTime"));
				wPosition.EditTime = StringUtils.parseCalendar(wSqlDataReader.get("EditTime"));
				wPosition.DepartmentID = StringUtils.parseInt(wSqlDataReader.get("DepartmentID"));
				wPosition.DepartmentName = StringUtils.parseString(wSqlDataReader.get("DepartmentName"));
				wPosition.Active = StringUtils.parseInt(wSqlDataReader.get("Active"));
				wPosition.DutyID = StringUtils.parseInt(wSqlDataReader.get("DutyID"));

				wPosition.Description = StringUtils.parseString(wSqlDataReader.get("Description"));
				if (StringUtils.isEmpty(wPosition.Code)) {
					wPosition.Code = StringUtils.Format("POS{0}", String.format("%07d", wPosition.ID));
				}
				wPositionList.add(wPosition);
			}

		} catch (Exception ex) {
			logger.error("BMS_QueryPositionList", ex);
			wErrorCode.set(ExceptionTypes.DBSQL.getValue());
		}
		return wPositionList;
	}

	public BMSPosition BMS_QueryPositionByID(BMSEmployee wLoginUser, int wID, String wCode,
			OutResult<Integer> wErrorCode) {
		wErrorCode.set(0);
		BMSPosition wResult = new BMSPosition();

		try {

			if (wID <= 0)
				return wResult;

		 

			List<BMSPosition> wList = null;
			if (wID > 0) {
				wList = this.BMS_QueryPositionList(wLoginUser, wID, "", -1, "",   -1, Pagination.getDefault(),
						wErrorCode);

			} else if (StringUtils.isNotEmpty(wCode)) {
				wList = this.BMS_QueryPositionList(wLoginUser, -1, wCode, -1, "",  -1, Pagination.getDefault(),
						wErrorCode);
			}
			if (wList != null && wList.size() > 0) {
				wResult = wList.get(0);
			}

		} catch (Exception ex) {
			logger.error("BMS_QueryPositionByID", ex);
			wErrorCode.set(ExceptionTypes.DBSQL.getValue());
		}
		return wResult;
	}

	public void BMS_DeletePosition(BMSEmployee wLoginUser, BMSPosition wPosition, OutResult<Integer> wErrorCode) {
		wErrorCode.set(0);

		try {

			if (wPosition == null || wPosition.ID <= 0) {
				wErrorCode.set(ExceptionTypes.Parameter.getValue());
				return;
			}

			Map<String, Object> wParams = new HashMap<String, Object>();

			wParams.put("ID", wPosition.ID);
			wParams.put("Active", 0);
			super.Delete(super.getTableName("bms_position"), wParams);

		} catch (Exception ex) {
			logger.error("BMS_DeletePosition", ex);
			wErrorCode.set(ExceptionTypes.DBSQL.getValue());
		}

	}
}
